package pl.edu.pw.perg.compinfodb.xls;

import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.LIST_TYPE;
import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.LOCATION_NOT_AVAILABLE;
import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.NUMERIC_TYPE;
import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.TEXT_TYPE;
import static pl.edu.pw.perg.compinfodb.xls.IXLSConstants.TRANSPARENT_TRUE;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TreeSet;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import pl.edu.pw.perg.compinfodb.entities.Connector;
import pl.edu.pw.perg.compinfodb.entities.ElementType;
import pl.edu.pw.perg.compinfodb.entities.InputBidirConnector;
import pl.edu.pw.perg.compinfodb.entities.OutputBidirConnector;
import pl.edu.pw.perg.compinfodb.entities.PhysicalElement;
import pl.edu.pw.perg.compinfodb.entities.VirtualElement;
import pl.edu.pw.perg.compinfodb.entities.field.Field;
import pl.edu.pw.perg.compinfodb.entities.field.ListField;
import pl.edu.pw.perg.compinfodb.entities.field.ListFieldItem;
import pl.edu.pw.perg.compinfodb.entities.field.NumericField;
import pl.edu.pw.perg.compinfodb.entities.field.TextField;

public class XLSMetadataReader {

  private static int FIRST_INDEX;
  private static int IC_OUT_COL;
  private static int IC_IN_COL;
  private static int IC_TYPE_COL;
  private static int E_IDENTIFIER_COL;
  private static int E_LABEL_COL;
  private static int E_TYPE_COL;
  private static int C_IN_CON_COL;
  private static int C_OUT_CON_COL;
  private static int C_IN_EL_COL;
  private static int C_OUT_EL_COL;
  private static int S_LOCATION_COL;
  private static int S_PARENT_COL;
  private static int S_TYPE_COL;
  private static int S_NAME_COL;
  private static int T_FIELDS_COL;
  private static int T_IN_CON_COL;
  private static int T_OUT_CON_COL;
  private static int T_TRANSPARENT_COL;
  private static int T_PARENT_COL;
  private static int T_NAME_COL;
  private static int F_MIN_LIST_ITEMS;
  private static int F_LIST_ITEMS_COL;
  private static int F_TYPE_COL;
  private static int F_NAME_COL;
  private static int HEADER_ROWS;

  private static final String CONNECTIONS_SHEET = "connections_sheet";
  private static final String CONNECTIONS_SHEET_LOCATION = "connections_sheet_location";
  private static final String INTERNAL_CONNECTIONS_SHEET = "internal_connections_sheet";
  private static final String INTERNAL_CONNECTIONS_SHEET_LOCATION = "internal_connections_sheet_location";
  private static final String ELEMENT_CABLES_SHEET = "element_cables_sheet";
  private static final String ELEMENT_CABLES_SHEET_LOCATION = "element_cables_sheet_location";
  private static final String ELEMENT_MODULES_SHEET = "element_modules_sheet";
  private static final String ELEMENT_MODULES_SHEET_LOCATION = "element_modules_sheet_location";
  private static final String STRUCTURE_CABLES_SHEET = "structure_cables_sheet";
  private static final String STRUCTURE_CABLES_SHEET_LOCATION = "structure_cables_sheet_location";
  private static final String STRUCTURE_MODULES_SHEET = "structure_modules_sheet";
  private static final String CONNECTOR_NAMES_SHEET_LOCATION = "connector_names_sheet_location";
  private static final String STRUCTURE_MODULES_SHEET_LOCATION = "structure_modules_sheet_location";
  private static final String CONNECTOR_NAMES_SHEET = "connector_names_sheet";
  private static final String HISTORY_SHEET = "history_sheet";
  private static final String HISTORY_SHEET_LOCATION = "history_sheet_location";
  private static final String TYPES_SHEET = "types_sheet";
  private static final String TYPES_SHEET_LOCATION = "types_sheet_location";
  private static final String FIELDS_SHEET = "fields_sheet";
  private static final String FIELDS_SHEET_LOCATION = "fields_sheet_location";
  private static final String CONNECTIONS_FILE = "connections_file";
  private static final String ELEMENT_CABLES_FILE = "element_cables_file";
  private static final String ELEMENT_MODULES_FILE = "element_modules_file";
  private static final String STRUCTURE_CABLES_FILE = "structure_cables_file";
  private static final String STRUCTURE_MODULES_FILE = "structure_modules_file";
  private static final String FIELDS_TYPES_HISTORY_FILE = "fields_types_history_file";
  private static final String PROPERTIES_FILE = "xls-validator.properties";

  private Sheet fieldsSheet;
  private Sheet typesSheet;
  private Sheet historySheet;
  private Sheet modulesSheet;
  private Sheet cablesSheet;
  private Sheet connectionsSheet;
  private Sheet elementModulesSheet;
  private Sheet elementCablesSheet;
  private Sheet internalConnectionSheet;
  private Sheet connectorNamesSheet;

  private Workbook wbookFTH;
  private Workbook wbookSM;
  private Workbook wbookSC;
  private Workbook wbookEM;
  private Workbook wbookEC;
  private Workbook wbookC;

  public static Properties props;
  static {
    props = new Properties();
    try {
      props.load(new FileInputStream(PROPERTIES_FILE));
    } catch (FileNotFoundException e) {
      System.err.println("File: " + PROPERTIES_FILE + " not found!");
      System.exit(1);
    } catch (IOException e) {
      System.err.println("I/O Exception occured reading file: "
          + PROPERTIES_FILE);
      System.exit(1);
    }

  }
  Map<String, Workbook> workbooks = new HashMap<String, Workbook>();

  private Map<String, Field> fieldMap = new HashMap<String, Field>();
  private Map<String, ElementType> typeMap = new HashMap<String, ElementType>();
  private Map<String, VirtualElement> elementMap = new HashMap<String, VirtualElement>();

  public XLSMetadataReader() {
    boolean correctInitialisation = true;
    try {
      wbookFTH = Workbook.getWorkbook(new File(props
          .getProperty(FIELDS_TYPES_HISTORY_FILE)));
      wbookSM = Workbook.getWorkbook(new File(props
          .getProperty(STRUCTURE_MODULES_FILE)));
      wbookSC = Workbook.getWorkbook(new File(props
          .getProperty(STRUCTURE_CABLES_FILE)));
      wbookEM = Workbook.getWorkbook(new File(props
          .getProperty(ELEMENT_MODULES_FILE)));
      wbookEC = Workbook.getWorkbook(new File(props
          .getProperty(ELEMENT_CABLES_FILE)));
      wbookC = Workbook.getWorkbook(new File(props
          .getProperty(CONNECTIONS_FILE)));
    } catch (BiffException e) {
      System.err.println("Cannot load spreadsheets!");
      System.err.println("Cause: " + e.getMessage());
      System.exit(1);
      e.printStackTrace();
    } catch (IOException e) {
      System.err.println("Cannot load spreadsheets!");
      System.err.println(e.getMessage());
      System.exit(1);
    }

    workbooks.put(FIELDS_TYPES_HISTORY_FILE, wbookFTH);
    workbooks.put(STRUCTURE_MODULES_FILE, wbookSM);
    workbooks.put(STRUCTURE_CABLES_FILE, wbookSC);
    workbooks.put(ELEMENT_MODULES_FILE, wbookEM);
    workbooks.put(ELEMENT_CABLES_FILE, wbookEC);
    workbooks.put(CONNECTIONS_FILE, wbookC);

    fieldsSheet = workbooks.get(
        props.getProperty(FIELDS_SHEET_LOCATION)).getSheet(
        props.getProperty(FIELDS_SHEET));
    if(fieldsSheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(FIELDS_SHEET))));
      correctInitialisation = false;
    }
    typesSheet = workbooks.get(
        props.getProperty(TYPES_SHEET_LOCATION)).getSheet(
        props.getProperty(TYPES_SHEET));
    if(typesSheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(TYPES_SHEET))));
      correctInitialisation = false;
    }
    historySheet = workbooks.get(
        props.getProperty(HISTORY_SHEET_LOCATION)).getSheet(
        props.getProperty(HISTORY_SHEET));
    if(historySheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(HISTORY_SHEET))));
      correctInitialisation = false;
    }
    connectorNamesSheet = workbooks.get(
        props.getProperty(CONNECTOR_NAMES_SHEET_LOCATION)).getSheet(
        props.getProperty(CONNECTOR_NAMES_SHEET));
    if(connectorNamesSheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(CONNECTOR_NAMES_SHEET))));
      correctInitialisation = false;
    }
    modulesSheet = workbooks.get(
        props.getProperty(STRUCTURE_MODULES_SHEET_LOCATION)).getSheet(
        props.getProperty(STRUCTURE_MODULES_SHEET));
    if(modulesSheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(STRUCTURE_MODULES_SHEET))));
      correctInitialisation = false;
    }
    cablesSheet = workbooks.get(
        props.getProperty(STRUCTURE_CABLES_SHEET_LOCATION)).getSheet(
        props.getProperty(STRUCTURE_CABLES_SHEET));
    if(cablesSheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(STRUCTURE_CABLES_SHEET))));
      correctInitialisation = false;
    }
    elementModulesSheet = workbooks.get(
        props.getProperty(ELEMENT_MODULES_SHEET_LOCATION)).getSheet(
        props.getProperty(ELEMENT_MODULES_SHEET));
    if(ELEMENT_MODULES_SHEET == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(ELEMENT_MODULES_SHEET))));
      correctInitialisation = false;
    }
    elementCablesSheet = workbooks.get(
        props.getProperty(ELEMENT_CABLES_SHEET_LOCATION)).getSheet(
        props.getProperty(ELEMENT_CABLES_SHEET));
    if(elementCablesSheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(ELEMENT_CABLES_SHEET))));
      correctInitialisation = false;
    }
    internalConnectionSheet = workbooks.get(
        props.getProperty(INTERNAL_CONNECTIONS_SHEET_LOCATION)).getSheet(
        props.getProperty(INTERNAL_CONNECTIONS_SHEET));
    if(internalConnectionSheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(INTERNAL_CONNECTIONS_SHEET))));
      correctInitialisation = false;
    }
    connectionsSheet = workbooks.get(
        props.getProperty(CONNECTIONS_SHEET_LOCATION)).getSheet(
        props.getProperty(CONNECTIONS_SHEET));
    if(connectionsSheet == null){
      handleFormatError(new InvalidMetadataFormatException("Sheet not found!", 
      XLSSheets.findSheet(props.getProperty(CONNECTIONS_SHEET))));
      correctInitialisation = false;
    }

    try {

      FIRST_INDEX = Integer.parseInt(props.getProperty("first_index"));
      IC_OUT_COL = convertCharToInt(props.getProperty("ic_out_col").charAt(0));
      IC_IN_COL = convertCharToInt(props.getProperty("ic_in_col").charAt(0));
      IC_TYPE_COL = convertCharToInt(props.getProperty("ic_type_col").charAt(0));
      E_IDENTIFIER_COL = convertCharToInt(props.getProperty("e_identifier_col").charAt(0));
      E_LABEL_COL = convertCharToInt(props.getProperty("e_label_col").charAt(0));
      E_TYPE_COL = convertCharToInt(props.getProperty("e_type_col").charAt(0));
      C_IN_CON_COL = convertCharToInt(props.getProperty("c_in_con_col").charAt(0));
      C_OUT_CON_COL = convertCharToInt(props.getProperty("c_out_con_col").charAt(0));
      C_IN_EL_COL = convertCharToInt(props.getProperty("c_in_el_col").charAt(0));
      C_OUT_EL_COL = convertCharToInt(props.getProperty("c_out_el_col").charAt(0));
      S_LOCATION_COL = convertCharToInt(props.getProperty("s_position_col").charAt(0));
      S_PARENT_COL = convertCharToInt(props.getProperty("s_parent_col").charAt(0));
      S_TYPE_COL = convertCharToInt(props.getProperty("s_type_col").charAt(0));
      S_NAME_COL = convertCharToInt(props.getProperty("s_identifier_col").charAt(0));
      T_FIELDS_COL = convertCharToInt(props.getProperty("t_fields_col").charAt(0));
      T_IN_CON_COL = convertCharToInt(props.getProperty("t_input_cons_col").charAt(0));
      T_OUT_CON_COL = convertCharToInt(props.getProperty("t_output_con_col").charAt(0));
      T_TRANSPARENT_COL = convertCharToInt(props
          .getProperty("t_transparent_col").charAt(0));
      T_PARENT_COL = convertCharToInt(props.getProperty("t_parent_col").charAt(0));
      T_NAME_COL = convertCharToInt(props.getProperty("t_element_type_col").charAt(0));
      F_MIN_LIST_ITEMS = Integer.parseInt(props
          .getProperty("f_minimum_list_items"));
      F_LIST_ITEMS_COL = convertCharToInt(props.getProperty("f_list_items_col").charAt(0));
      F_TYPE_COL = convertCharToInt(props.getProperty("f_field_type_col").charAt(0));
      F_NAME_COL = convertCharToInt(props.getProperty("f_field_name_col").charAt(0));
      HEADER_ROWS = Integer.parseInt(props.getProperty("header_rows"));

    } catch (NumberFormatException e) {
      System.err
          .println("One of numeric properties is not a number. Check properties file!");
      System.err.println(e.getMessage());
      System.exit(1);
    }
    if(!correctInitialisation)
      System.exit(1);

  }

  public List<Field> getFields() throws InvalidMetadataFormatException {
    XLSSheets sheet = XLSSheets.findSheet(fieldsSheet.getName());
    if (fieldsSheet == null) {
      handleFormatError(new InvalidMetadataFormatException(
          "Sheet not found! Check sheets names. ", sheet));
      return null;
    }
    List<Field> fields = new ArrayList<Field>();
    for (int i = HEADER_ROWS; i < fieldsSheet.getRows(); i++) {
      String name = fieldsSheet.getCell(F_NAME_COL, i).getContents();
      String type = fieldsSheet.getCell(F_TYPE_COL, i).getContents();
      if (name.equals("") && type.equals(""))
        continue;
      if (name.equals(""))
        handleFormatError(new InvalidMetadataFormatException(
            "Field name cannot be empty!", sheet, String.valueOf(convertIntToChar(F_NAME_COL)).toUpperCase(), i));

      if (type.equals(""))
        handleFormatError(new InvalidMetadataFormatException(
            "Field type cannot be empty!", sheet, String.valueOf(convertIntToChar(F_TYPE_COL)).toUpperCase(), i));
      Field field;
      if (type.equals(TEXT_TYPE))
        field = new TextField();
      else if (type.equals(NUMERIC_TYPE))
        field = new NumericField();
      else if (type.equals(LIST_TYPE)) {
        field = new ListField();
        Set<ListFieldItem> items = new TreeSet<ListFieldItem>();
        int position = 0;
        for (int j = F_LIST_ITEMS_COL; j < fieldsSheet.getColumns(); j++) {
          String listItem = fieldsSheet.getCell(j, i).getContents();
          if (listItem.equals("")) {
            if (j < F_LIST_ITEMS_COL + F_MIN_LIST_ITEMS)
              handleFormatError(new InvalidMetadataFormatException(
                  "List field should define at least 2 list " + "items!",
                  sheet, String.valueOf(convertIntToChar(j)).toUpperCase(), i));
            break;
          }
          ListFieldItem lfi = new ListFieldItem(listItem);
          lfi.setPosition(position++);
          items.add(lfi);
        }
        ((ListField) field).setListFieldItems(items);
      } else {
        handleFormatError(new InvalidMetadataFormatException(
            "Wrong field type - should " + "be one of:" + TEXT_TYPE + ", "
                + NUMERIC_TYPE + ", " + LIST_TYPE + " but was: " + type, sheet,
                String.valueOf(convertIntToChar(F_TYPE_COL)).toUpperCase(), i));
        continue;
      }
      field.setFieldName(name);
      fieldMap.put(name, field);
      fields.add(field);
    }
    return fields;
  }

  public List<ElementType> getTypes() throws InvalidMetadataFormatException {
    XLSSheets sheet = XLSSheets.findSheet(typesSheet.getName());
    Map<ElementType, String> parents = new HashMap<ElementType, String>();
    if (typesSheet == null) {
      handleFormatError(new InvalidMetadataFormatException(
          "Sheet not found! Check sheets names. ", sheet));
      return null;
    }
    List<ElementType> types = new ArrayList<ElementType>();
    for (int i = HEADER_ROWS; i < typesSheet.getRows(); i++) {
      String name = typesSheet.getCell(T_NAME_COL, i).getContents();
      String parentName = typesSheet.getCell(T_PARENT_COL, i).getContents();
      String isTransparent = typesSheet.getCell(T_TRANSPARENT_COL, i)
          .getContents();
      String outConnNr = typesSheet.getCell(T_OUT_CON_COL, i).getContents();
      if (name.equals("") && parentName.equals("") && isTransparent.equals("")
          && outConnNr.equals(""))
        continue;
      ElementType type = new ElementType();
      if (name.equals(""))
        handleFormatError(new InvalidMetadataFormatException(
            "Type name cannot be empty!", sheet, String.valueOf(convertIntToChar(T_NAME_COL)).toUpperCase(), i));
      type.setTypeName(name);
      typeMap.put(name, type);
      parents.put(type, parentName);
      if (!isTransparent.equals("") && !isTransparent.equals(TRANSPARENT_TRUE))
        handleFormatError(new InvalidMetadataFormatException(
            "Transparent flag should be " + TRANSPARENT_TRUE + " or empty",
            sheet, String.valueOf(convertIntToChar(T_TRANSPARENT_COL)).toUpperCase(), i));
      type.setTransparent(isTransparent.equals(TRANSPARENT_TRUE));

      String inConnNr = typesSheet.getCell(T_IN_CON_COL, i).getContents();
      if (!inConnNr.equals(""))
        try {
          type.setInputConnectorsNr(Integer.parseInt(inConnNr));
        } catch (NumberFormatException e) {
          handleFormatError(new InvalidMetadataFormatException(
              "Number of input connectors should be an integer number!", sheet,
              String.valueOf(convertIntToChar(T_IN_CON_COL)).toUpperCase(), i));
        }
      if (!outConnNr.equals(""))
        type.setOutputConnectorsNr(Integer.parseInt(outConnNr));

      Set<Field> fields = new HashSet<Field>();
      for (int j = T_FIELDS_COL; j < typesSheet.getColumns(); j++) {
        String fieldName = typesSheet.getCell(j, i).getContents();
        if (fieldName.equals(""))
          break;
        Field field = fieldMap.get(fieldName);
        if (field == null)
          handleFormatError(new InvalidMetadataFormatException("Field named "
              + fieldName + " not found.", sheet, String.valueOf(convertIntToChar(j)).toUpperCase(), i));
        fields.add(field);
      }
      type.setFields(fields);
      types.add(type);
    }
    for (ElementType elementType : types) {
      String parentName = parents.get(elementType);
      elementType.setParentType(typeMap.get(parentName));
    }
    return types;
  }

  private List<VirtualElement> getElementsStructure(Sheet structureSheet)
      throws InvalidMetadataFormatException {
    XLSSheets sheet = XLSSheets.findSheet(structureSheet.getName());
    if (structureSheet == null) {
      handleFormatError(new InvalidMetadataFormatException(
          "Sheet not found! Check sheets names. ", sheet));
      return null;
    }
    Map<VirtualElement, String> parents = new HashMap<VirtualElement, String>();
    List<VirtualElement> rows = new ArrayList<VirtualElement>();
    List<VirtualElement> elements = new ArrayList<VirtualElement>();
    for (int i = HEADER_ROWS; i < structureSheet.getRows(); i++) {
      String name = structureSheet.getCell(S_NAME_COL, i).getContents();
      String typeName = structureSheet.getCell(S_TYPE_COL, i).getContents();
      String parentName = structureSheet.getCell(S_PARENT_COL, i).getContents();
      if (name.equals("") && typeName.equals("") && parentName.equals(""))
        continue;
      if (name.equals("")) {
        handleFormatError(new InvalidMetadataFormatException(
            "Element identifier cannot be empty!", sheet, String.valueOf(convertIntToChar(S_NAME_COL)).toUpperCase(), i));
      }
      if (typeName.equals(""))
        handleFormatError(new InvalidMetadataFormatException(
            "Element type name cannot be empty!", sheet, String.valueOf(convertIntToChar(S_TYPE_COL)).toUpperCase(), i));
      ElementType et = typeMap.get(typeName);
      if (et == null) {
        handleFormatError(new InvalidMetadataFormatException(
            "Element type named " + typeName + " not found.", sheet,
            String.valueOf(convertIntToChar(S_TYPE_COL)).toUpperCase(), i));
        continue;
      }
      VirtualElement ve = new VirtualElement(et);
      parents.put(ve, parentName);
      rows.add(i, ve);
      ve.setName(name);
      elementMap.put(name, ve);
      elements.add(ve);
      int location = LOCATION_NOT_AVAILABLE;
      String locationStr = structureSheet.getCell(S_LOCATION_COL, i)
          .getContents();

      try {
        if (!locationStr.equals(""))
          location = Integer.parseInt(locationStr);
      } catch (NumberFormatException e) {
        handleFormatError(new InvalidMetadataFormatException("Position "
            + "should be an long integer value! Was: " + locationStr, sheet,
            String.valueOf(convertIntToChar(S_LOCATION_COL)).toUpperCase(), i, e));
      }
      ve.setLocation(location);
      if (et.isTransparent())
        connectInternally(ve);
      else {
        initializeConnectors(ve);
      }
    }
    for (VirtualElement virtualElement : elements) {
      String parentName = parents.get(virtualElement);
      int i = rows.indexOf(virtualElement);
      VirtualElement parent = elementMap.get(parentName);
      if (!parentName.equals("") && parent == null)
        handleFormatError(new InvalidMetadataFormatException(
            "Element's parent, named "
                + parentName
                + " not found.",
            sheet, String.valueOf(convertIntToChar(S_PARENT_COL)).toUpperCase(), i));
      virtualElement.setParent(parent);
    }
    return elements;
  }

  private void initializeConnectors(VirtualElement ve) {
    Set<Connector> input = new TreeSet<Connector>();
    Set<Connector> output = new TreeSet<Connector>();
    for (int j = 0; j < ve.getElementType().getInputConnectorsNr(); j++) {
      Connector c = new Connector(j, true, ve);
      input.add(c);
      c.setElement(ve);
    }

    for (int j = 0; j < ve.getElementType().getOutputConnectorsNr(); j++) {
      Connector c = new Connector(j, false, ve);
      output.add(c);
      c.setElement(ve);
    }
    ve.setInputConnectors(input);
    ve.setOutputConnectors(output);
  }

  public List<Connector> getConnections() throws InvalidMetadataFormatException {
    XLSSheets sheet = XLSSheets.findSheet(connectionsSheet.getName());
    if (connectionsSheet == null) {
      handleFormatError(new InvalidMetadataFormatException(
          "Sheet not found! Check sheets names. ", sheet));
      return null;
    }
    List<Connector> connectors = new ArrayList<Connector>();
    for (int i = HEADER_ROWS; i < connectionsSheet.getRows(); i++) {
      String outElementName = connectionsSheet.getCell(C_OUT_EL_COL, i)
          .getContents();
      String inElementName = connectionsSheet.getCell(C_IN_EL_COL, i)
          .getContents();
      if (outElementName.equals("") && inElementName.equals(""))
        continue;
      if (outElementName.equals(""))
        handleFormatError(new InvalidMetadataFormatException("Element name "
            + "cannot be null.", sheet, String.valueOf(convertIntToChar(C_OUT_EL_COL)).toUpperCase(), i));
      VirtualElement outputElt = elementMap.get(outElementName);
      if (outputElt == null) {
        handleFormatError(new InvalidMetadataFormatException("Element "
            + outElementName + " undefined.", sheet, String.valueOf(convertIntToChar(C_OUT_EL_COL)).toUpperCase(), i));
        continue;
      }

      if (inElementName.equals("")) {
        handleFormatError(new InvalidMetadataFormatException("Element name "
            + "cannot be null.", sheet, String.valueOf(convertIntToChar(C_IN_EL_COL)).toUpperCase(), i));
        continue;
      }
      VirtualElement inputElt = elementMap.get(inElementName);
      if (inputElt == null) {
        handleFormatError(new InvalidMetadataFormatException("Element "
            + inElementName + " undefined.", sheet, String.valueOf(convertIntToChar(C_IN_EL_COL)).toUpperCase(), i));
        continue;
      }

      int outConnNr = FIRST_INDEX;
      int inConnNr = FIRST_INDEX;
      String outStr = connectionsSheet.getCell(C_OUT_CON_COL, i).getContents();
      if (!outStr.equals("")) {
        try {
          outConnNr = Integer.parseInt(outStr);
        } catch (NumberFormatException e) {
          handleFormatError(new InvalidMetadataFormatException(
              "Output connector index "
                  + "should be an long integer value! Count starts with "
                  + FIRST_INDEX, sheet, String.valueOf(convertIntToChar(C_OUT_CON_COL)).toUpperCase(), i, e));
          continue;
        }
      }
      String inStr = connectionsSheet.getCell(C_IN_CON_COL, i).getContents();
      if (!inStr.equals("")) {
        try {
          inConnNr = Integer.parseInt(inStr);
        } catch (NumberFormatException e) {
          handleFormatError(new InvalidMetadataFormatException(
              "Input connector index "
                  + "should be an long integer value! Count starts with "
                  + FIRST_INDEX, sheet, String.valueOf(convertIntToChar(C_IN_CON_COL)).toUpperCase(), i, e));
          continue;
        }
      }

      Connector c1 = getConnectorAt(outConnNr, outputElt.getOutputConnectors()
          .iterator());
      Connector c2 = getConnectorAt(inConnNr, inputElt.getInputConnectors()
          .iterator());
      c1.setExternalConnectorId(c2.getId());
      c2.setExternalConnectorId(c1.getId());
      connectors.add(c1);
      connectors.add(c2);
    }

    return connectors;
  }

  private List<PhysicalElement> getPhysicalElements(Sheet physicalSheet)
      throws InvalidMetadataFormatException {
    XLSSheets sheet = XLSSheets.findSheet(physicalSheet.getName());
    if (physicalSheet == null) {
      handleFormatError(new InvalidMetadataFormatException(
          "Sheet not found! Check sheets names. ", sheet));
      return null;
    }
    List<PhysicalElement> phElements = new ArrayList<PhysicalElement>();
    for (int i = HEADER_ROWS; i < physicalSheet.getRows(); i++) {
      String elementTypeName = physicalSheet.getCell(E_TYPE_COL, i)
          .getContents();
      String label = physicalSheet.getCell(E_LABEL_COL, i).getContents();
      String virtualElementName = physicalSheet.getCell(E_IDENTIFIER_COL, i)
          .getContents();
      if (elementTypeName.equals("") && label.equals("")
          && virtualElementName.equals(""))
        continue;
      if (elementTypeName.equals(""))
        handleFormatError(new InvalidMetadataFormatException("Element type "
            + "cannot be null.", sheet, String.valueOf(convertIntToChar(E_TYPE_COL)).toUpperCase(), i));
      PhysicalElement pe = new PhysicalElement();
      ElementType et = typeMap.get(elementTypeName);
      if (et == null)
        handleFormatError(new InvalidMetadataFormatException("Element type "
            + elementTypeName + " undefined.", sheet, String.valueOf(convertIntToChar(E_TYPE_COL)).toUpperCase(), i));
      pe.setElementType(et);
      if (label.equals(""))
        handleFormatError(new InvalidMetadataFormatException("Element label "
            + "cannot be null.", sheet, String.valueOf(convertIntToChar(E_LABEL_COL)).toUpperCase(), i));
      pe.setLabel(label);
      if (virtualElementName.equals(""))
        handleFormatError(new InvalidMetadataFormatException("Identifier "
            + "cannot be null.", sheet, String.valueOf(convertIntToChar(E_IDENTIFIER_COL)).toUpperCase(), i));
      VirtualElement ve = elementMap.get(virtualElementName);
      if (ve == null)
        handleFormatError(new InvalidMetadataFormatException("Identifier "
            + virtualElementName + " not found.", sheet, String.valueOf(convertIntToChar(E_IDENTIFIER_COL)).toUpperCase(), i));
      pe.setVirtualElement(ve);
      phElements.add(pe);
    }
    return phElements;
  }

  private void connectInternally(VirtualElement ve) {
    XLSSheets sheet = XLSSheets.findSheet(internalConnectionSheet.getName());
    if (internalConnectionSheet == null) {
      handleFormatError(new InvalidMetadataFormatException(
          "Sheet not found! Check sheets names. ", sheet));
      return;
    }
    Set<Connector> input = new TreeSet<Connector>();
    Set<Connector> output = new TreeSet<Connector>();

    for (int i = HEADER_ROWS; i < internalConnectionSheet.getRows(); i++) {
      String elementName = internalConnectionSheet.getCell(IC_TYPE_COL, i)
          .getContents();
      if (elementName.equals(""))
        continue;
      ElementType et = typeMap.get(elementName);
      if (et == null)
        handleFormatError(new InvalidMetadataFormatException("Undefined type: "
            + elementName, sheet, String.valueOf(convertIntToChar(IC_TYPE_COL)).toUpperCase(), i));
      if (!et.equals(ve.getElementType()))
        continue;

      for (int j = 0; j < ve.getElementType().getInputConnectorsNr(); j++) {
        input.add(new InputBidirConnector(j, ve));
      }

      for (int j = 0; j < ve.getElementType().getOutputConnectorsNr(); j++) {
        output.add(new OutputBidirConnector(j, ve));
      }

      int inIndex;
      try {
        inIndex = Integer.parseInt(internalConnectionSheet
            .getCell(IC_IN_COL, i).getContents());
      } catch (NumberFormatException e) {
        handleFormatError(new InvalidMetadataFormatException(
            "Input connector index "
                + "should be an long integer value! Count starts with 0.",
            sheet, String.valueOf(convertIntToChar(IC_IN_COL)).toUpperCase(), i, e));
        continue;
      }
      int outIndex;
      try {
        outIndex = Integer.parseInt(internalConnectionSheet.getCell(IC_OUT_COL,
            i).getContents());
      } catch (NumberFormatException e) {
        handleFormatError(new InvalidMetadataFormatException(
            "Output connector index "
                + "should be an long integer value! Count starts with 0.",
            sheet, String.valueOf(convertIntToChar(IC_OUT_COL)).toUpperCase(), i, e));
        continue;

      }
      if (inIndex >= input.size()) {
        handleFormatError(new InvalidMetadataFormatException(
            "Input connector index: " + inIndex + "\nout of range: 0-"
                + (input.size() - 1) + ".\n", sheet, String.valueOf(convertIntToChar(IC_IN_COL)).toUpperCase(), i));
        continue;
      }
      if (outIndex >= output.size()) {
        handleFormatError(new InvalidMetadataFormatException(
            "Output connector index: " + outIndex + "\nout of range: 0-"
                + (input.size() - 1) + ".\n", sheet, String.valueOf(convertIntToChar(IC_OUT_COL)).toUpperCase(), i));
        continue;
      }
      InputBidirConnector inConn = (InputBidirConnector) getConnectorAt(
          inIndex, input.iterator());
      OutputBidirConnector outConn = (OutputBidirConnector) getConnectorAt(
          outIndex, output.iterator());
      inConn.getInternalConnections().add(outConn);
      outConn.getInternalConnections().add(inConn);
    }
    for (Connector connector : input) {
      InputBidirConnector in = (InputBidirConnector) connector;
      if (in.getInternalConnections().size() == 0)
        handleFormatError(new InvalidMetadataFormatException(
            "Warnig: Input bidirectional connector " + in.getPosition()
                + "\nfrzom type: " + ve.getElementType().getTypeName()
                + "\nis not connected internally.\nIt will be considered as "
                + "unidirectional.", sheet));
    }

    for (Connector connector : output) {
      OutputBidirConnector out = (OutputBidirConnector) connector;
      if (out.getInternalConnections().size() == 0)
        handleFormatError(new InvalidMetadataFormatException(
            "Warnig: Output bidirectional connector " + out.getPosition()
                + "\nfrom type: " + ve.getElementType().getTypeName()
                + "\nis not connected internally.\nIt will be considered as "
                + "unidirectional.", sheet));
    }
    ve.setInputConnectors(input);
    ve.setOutputConnectors(output);
  }

  @SuppressWarnings("unchecked")
  private static Connector getConnectorAt(int index, Iterator it) {
    Connector conn = null;
    for (int i = FIRST_INDEX; i <= index - FIRST_INDEX; i++) {
      conn = (Connector) it.next();
    }
    if(conn == null)
      throw new IndexOutOfBoundsException("No such connector with index: " + index);
    return conn;
  }

  protected void handleFormatError(InvalidMetadataFormatException e) {
    throw e;
  }

  public List<VirtualElement> getElementsStructure() {
    List<VirtualElement> structure = getElementsStructure(modulesSheet);
    structure.addAll(getElementsStructure(cablesSheet));
    return structure;
  }

  public List<PhysicalElement> getPhysicalElements() {  
    List<PhysicalElement> elements = getPhysicalElements(elementModulesSheet);
    elements.addAll(getPhysicalElements(elementCablesSheet));
    return elements;
  }

  protected enum XLSSheets {

    FIELDS(props.getProperty(FIELDS_SHEET)), 
    TYPES(props.getProperty(TYPES_SHEET)), 
    CONNECTIONS(props.getProperty(CONNECTIONS_SHEET)), 
    STRUCTURE_MODULES(props.getProperty(STRUCTURE_MODULES_SHEET)), 
    STRUCTURE_CABLES(props.getProperty(STRUCTURE_CABLES_SHEET)), 
    INTERNAL(props.getProperty(INTERNAL_CONNECTIONS_SHEET)), 
    ELEMENT_MODULES(props.getProperty(ELEMENT_MODULES_SHEET)), 
    ELEMENT_CABLES(props.getProperty(ELEMENT_CABLES_SHEET)), 
    CONNECTOR_NAMES(props.getProperty(CONNECTOR_NAMES_SHEET)), 
    HISTORY(props.getProperty(HISTORY_SHEET));

    private String sheetName;

    XLSSheets(String sheetName) {
      this.sheetName = sheetName;
    }

    public String getName() {
      return sheetName;
    }

    public static XLSSheets findSheet(String name) {
      for (XLSSheets sheet : XLSSheets.values()) {
        if (sheet.getName().equals(name))
          return sheet;
      }
      System.out.println("No sheet with name: " + name);
      return null;
    }
    
  }
  static int convertCharToInt(char c){
    return Character.getNumericValue(c) - 10;
  }
  
  static char convertIntToChar(int i){
    return Character.forDigit(i + 10, 25);
  }
}
